package com.amado.course45mysql02.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.amado.course45mysql02.domain.Student;

public class EmployeeQueryDao {
//	查询得到一个对象
	public Student findById(long id) {
		String sql = "select id,name,sex,phone,age,create_time from t_student where id=" + id;
		Student student = new Student();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = DBConnection.getConn();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				student.setId(rs.getLong(1));
				student.setName(rs.getString(2));
				student.setSex(rs.getByte(3));
				student.setPhone(rs.getString(4));
				student.setAge(rs.getShort(5));
				student.setCreate_time(rs.getDate(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBConnection.close(rs, ps, conn);
		return student;
	}
//	查询得到一组数据
	@SuppressWarnings("unchecked")
	public List<Student> find() {
		String sql = "select * from t_student";
		Connection conn = DBConnection.getConn();
		List<Student> list = new ArrayList();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			//rs结果集
			while (rs.next()) {
				Student student = new Student();
				student.setId(rs.getLong(1));
				student.setName(rs.getString(2));
				student.setSex(rs.getByte(3));
				student.setPhone(rs.getString(4));
				student.setAge(rs.getShort(5));
				student.setCreate_time(rs.getDate(6));
				//循环添加到List
				list.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBConnection.close(rs, ps, conn);
		return list;
	}
	@SuppressWarnings("unchecked")
	public long findCount() {
		String sql = "select count(1) from t_student";
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getLong(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBConnection.close(rs, ps, conn);
		}
		
		return 0;
	}

	public static void main(String[] args) {
		EmployeeQueryDao employeeDao = new EmployeeQueryDao();
		Student student = employeeDao.findById(28);
		System.out.println(student);
		List<Student> listStudent = employeeDao.find();
		for(Student stud:listStudent){
			System.out.println(stud);
		}
		System.out.println("数量"+employeeDao.findCount());
	}
}
